﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QLTour.Ultilities;
using QLTour.Biz;
using System.Data.SqlClient;
using System.Data;

namespace QLTour.Dal
{
    public class TourDal
    {
        public TourDal()
        {

        }
        DatabaseHelper db = new DatabaseHelper();
        public List<TourBiz> GetList()
        {
            List<TourBiz> list = new List<TourBiz>();
            SqlDataReader reader = db.Reader("SELECT * FROM [Tour]");
            while (reader.Read())
            {
                TourBiz tour = new TourBiz();
                tour.MaTour = reader["MaTour"].ToString();
                tour.Ten = reader["Ten"].ToString();
                tour.TrongNuoc = (bool)reader["TrongNuoc"];
                list.Add(tour);
            }
            return list;
        }
        
        public Boolean Insert(TourBiz tour)
        {
            int rs = db.ExecuteNonQuery("INSERT INTO dbo.[Tour]([MaTour],[Ten],[TrongNuoc]) VALUES (N'" + tour.MaTour + "',N'" + tour.Ten + "','"+tour.TrongNuoc+"')");
            return rs > 0;
        }

        public Boolean Update(TourBiz tour)
        {
            string query = " UPDATE [Tour]" +
                           " SET [Ten] =  N'" + tour.Ten + "'," +" [TrongNuoc] =  N'" + tour.TrongNuoc + "'"+
                           " WHERE [MaTour] = N'" + tour.MaTour + "'";
            int rs = db.ExecuteNonQuery(query);
            return rs > 0;
        }

        public List<TourBiz> GetAllTours()
        {
            List<TourBiz> lst = new List<TourBiz>();
            string query = "SELECT * FROM [Tour]";
            DataTable tbl = db.GetDateTable(query);

            foreach (DataRow r in tbl.Rows)
            {
                TourBiz tour = new TourBiz();
                tour.MaTour = (string)r["MaTour"];
                tour.Ten = (string)r["Ten"];
                tour.TrongNuoc = (bool)r["TrongNuoc"];

                lst.Add(tour);
            }

            return lst;
        }

        public List<DiaDiemBiz> GetAllDiaDiem(TourBiz tour)
        {
            List<DiaDiemBiz> lst = new List<DiaDiemBiz>();
            string query = "SELECT [DiaDiem].[MaDiaDiem], [DiaDiem].[Ten] FROM [ThamQuan], [DiaDiem] WHERE [MaTour]='" + tour.MaTour + "' AND [ThamQuan].[MaDiaDiem]=[DiaDiem].[MaDiaDiem] ORDER BY [ThamQuan].[ThuTu]";
            DataTable tbl = db.GetDateTable(query);

            foreach (DataRow r in tbl.Rows)
            {
                DiaDiemBiz dd = new DiaDiemBiz();
                dd.MaDiaDiem = (int)r["MaDiaDiem"];
                dd.Ten = (string)r["Ten"];

                lst.Add(dd);
            }

            return lst;
        }

        public List<GiaTourBiz> GetAllGiaTour(TourBiz tour)
        {
            List<GiaTourBiz> lst = new List<GiaTourBiz>();
            string query = "SELECT * FROM [GiaTour] WHERE [MaTour]='" + tour.MaTour + "'";
            DataTable tbl = db.GetDateTable(query);

            foreach (DataRow r in tbl.Rows)
            {
                GiaTourBiz giatour = new GiaTourBiz();
                giatour.MaGia = (int)r["MaGia"];
                giatour.Ten = r["Ten"].ToString();
                giatour.GiaTri = (int)r["GiaTri"];
                giatour.BatDau=(DateTime)r["BatDau"];
                giatour.KetThuc = (DateTime)r["KetThuc"];
                giatour.MaTour = tour.MaTour;
                lst.Add(giatour);
            }
            return lst;
        }

        public int XoaHetGia(TourBiz tourBiz)
        {
            string query = "DELETE FROM [GiaTour] WHERE [MaTour]='" + tourBiz.MaTour + "'";
            return db.ExecuteNonQuery(query);
        }

        public bool XoaTour(TourBiz tourBiz)
        {
            string query = "DELETE FROM [Tour] WHERE [MaTour]='" + tourBiz.MaTour + "'";
            return db.ExecuteNonQuery(query) > 0;
        }
    }
}
